package cn.edu.sdjzu.xg.eagle.dao;

import cn.edu.sdjzu.xg.eagle.entity.Department;
import cn.edu.sdjzu.xg.eagle.entity.School;
import cn.edu.sdjzu.xg.eagle.exception.BaseException;
import cn.edu.sdjzu.xg.eagle.util.JdbcHelper;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Collection;
import java.util.HashSet;


public class DepartmentImpl implements DepartmentInterface {
    private static DepartmentImpl departmentDao=null;
    public DepartmentImpl() {}
    public static DepartmentInterface getInstance(){
        if (departmentDao==null){
            departmentDao=new DepartmentImpl();
        }
        return departmentDao;
    }

    @Override
    public Department find(Long id) throws SQLException, BaseException {
        Department department = null;
        Connection connection = JdbcHelper.getConn();
        String sql = "select * from department where id = ?";
        PreparedStatement pstmt_select_id = connection.prepareStatement(sql);
        pstmt_select_id.setLong(1, id);
        ResultSet resultSet_select_id = pstmt_select_id.executeQuery();
        if(resultSet_select_id.next()){
            String description = resultSet_select_id.getString("description");
            String no = resultSet_select_id.getString("no");
            String remarks = resultSet_select_id.getString("remarks");
            Long school_id = resultSet_select_id.getLong("school_id");
            SchoolDaoImpl schoolDaoImpl = new SchoolDaoImpl();
            School school = schoolDaoImpl.find(school_id);
            department = new Department(id, description, no, remarks,school);
        }
        JdbcHelper.close(resultSet_select_id,pstmt_select_id,connection);
        return department;
    }

    @Override
    public Collection<Department> findAll() throws SQLException, BaseException {
        Collection<Department> departments = new HashSet<Department>();
        Connection connection = JdbcHelper.getConn();
        StringBuilder sql_select = new StringBuilder("select * from department");
        PreparedStatement pstmt_select_all = connection.prepareStatement(sql_select.toString());
        ResultSet resultSet = pstmt_select_all.executeQuery();
        while(resultSet.next()){
            Long id = resultSet.getLong("id");
            String description = resultSet.getString("description");
            String no = resultSet.getString("no");
            String remarks = resultSet.getString("remarks");
            Long school_id = resultSet.getLong("school_id");
            SchoolDaoImpl schoolDaoImpl = new SchoolDaoImpl();
            School school = schoolDaoImpl.find(school_id);
            Department department = new Department(id, description, no, remarks, school);
            departments.add(department);
        }
        return departments;
    }

    @Override
    public boolean add(Department department) throws SQLException, BaseException {
        Connection connection = JdbcHelper.getConn();
        String sql_insert = "insert into department (description,no,remarks,school_id) values(?,?,?,?)";
        PreparedStatement pstmt_insert = connection.prepareStatement(sql_insert);
        pstmt_insert.setString(1, department.getDescription());
        pstmt_insert.setString(2, department.getNo());
        pstmt_insert.setString(3, department.getRemarks());
        pstmt_insert.setLong(4, department.getSchool().getId());

        int rowAffected = pstmt_insert.executeUpdate();
        return rowAffected>0;
    }

    @Override
    public boolean update(Department department) throws SQLException, BaseException {
        String sql_update="Update department SET description = ?,no = ?,remarks = ? where id = ?";
        Connection connection = JdbcHelper.getConn();
        PreparedStatement pstmt_update = connection.prepareStatement(sql_update);
        pstmt_update.setString(1, department.getDescription());
        pstmt_update.setString(2, department.getNo());
        pstmt_update.setString(3, department.getRemarks());
        pstmt_update.setLong(4, department.getId());
        int rowAffected = pstmt_update.executeUpdate();
        JdbcHelper.close(pstmt_update,connection);
        return rowAffected>0;
    }

    @Override
    public boolean delete(Long id) throws SQLException, BaseException {
        String sql_delete="delete from department where id = ?";
        Connection connection = JdbcHelper.getConn();
        PreparedStatement pstmt_delete = connection.prepareStatement(sql_delete);
        pstmt_delete.setLong(1, id);
        int rowAffected = pstmt_delete.executeUpdate();
        JdbcHelper.close(pstmt_delete,connection);
        return rowAffected>0;
    }

    @Override
    public int countAll(School school) throws SQLException, BaseException {
        Connection connection = JdbcHelper.getConn();
        String sql_count_by_school_id = "select count(id) as cnt_by_school_id from department where school_id = ?";
        PreparedStatement preparedStatement = connection.prepareStatement(sql_count_by_school_id);
        preparedStatement.setLong(1, school.getId());
        int count_by_school_id = 0;
        ResultSet resultSet = preparedStatement.executeQuery();
        if(resultSet.next()){
            count_by_school_id = resultSet.getInt("cnt_by_school_id");
            JdbcHelper.close(resultSet,preparedStatement,connection);
        }
        return count_by_school_id;
    }

    public static void main(String[] args) throws SQLException {
        Connection conn = JdbcHelper.getConn();
        DepartmentInterface departmentDao = DepartmentImpl.getInstance();
        School school = new School(9L,"山建","01","好");
        SchoolDaoImpl schoolDaoImpl = new SchoolDaoImpl();

        System.out.println(schoolDaoImpl.add(conn,school));

        Department department = new Department(14L,"管院","07","好 ",school);

        System.out.println(departmentDao.add(department));
        System.out.println(departmentDao.update(department));
        System.out.println(departmentDao.find(department.getId()));
        System.out.println(departmentDao.findAll());
        System.out.println(departmentDao.delete(department.getId()));
    }
}
